package org.deschutter.dump;

import org.deschutter.Constants;
import org.deschutter.URLReader;
import org.deschutter.Queries;
import org.deschutter.exceptions.CurrenttickNotFoundException;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import java.net.MalformedURLException;
import java.sql.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.math.BigDecimal;

/**
 * User: Berten De Schutter
 * Date: 06-Jul-2009
 * Time: 18:17:19
 */
public class AllianceDump implements Constants, Job {


    public static void loadFileinDB(String file, Connection conn) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();
        st.executeUpdate("DELETE FROM alliances_add");

        rs = st.executeQuery("LOAD DATA LOCAL INFILE '" + file + "' into table alliances_add fields optionally enclosed by '\"' ignore 7 lines");

        rs.close();
        st.close();
    }

    public static void calculateSizeRank(Connection conn) throws SQLException {
        Statement st = null;

        st = conn.createStatement();

        st.executeUpdate("DELETE FROM alliance_rank_size_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO alliance_rank_size_temp" +
                " SELECT @rank:=@rank+1 AS rank,`name`" +
                " FROM alliances_add" +
                " order by `size` desc;");

        st.close();
    }

    public static void populateHistory(Connection conn, String tick) throws SQLException {

        Statement st = null;


        String query = "INSERT INTO alliances_history" +
                " (SELECT " +
                "a.rank," +
                "a.name," +
                "a.size," +
                "a.members," +
                "a.score," +
                tick + " - 1," +
                "sr.RANK " +

                "FROM alliances a " +
                "INNER JOIN alliance_rank_size sr ON (sr.name=a.name))";


        st = conn.createStatement();

        int a = st.executeUpdate(query);

        st.close();
    }

    public static void populateCurrentTables(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();
        st.executeUpdate("DELETE FROM alliances");
        st.executeUpdate("INSERT INTO alliances SELECT * from alliances_add");

        st.executeUpdate("DELETE FROM alliance_rank_size");
        st.executeUpdate("INSERT INTO alliance_rank_size SELECT * from alliance_rank_size_temp");

        st.close();

    }


    public static void runTickDump(String alliance_listing_url) {
        double time = System.currentTimeMillis();


        Connection conn = null;
        try {

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);

            // Step 1:  Get alliance_listing
            URLReader.copy(alliance_listing_url, LOCAL_ALLIANCE_LISTING);

            // Step 2: Get current Tick ( from planet dump)
            String dump = URLReader.get(alliance_listing_url);
            Pattern compiledRegex;
            Matcher regexMatcher;
            compiledRegex = Pattern.compile("Tick: (\\d+)\\s*");
            regexMatcher = compiledRegex.matcher(dump.replace(",", ""));
            regexMatcher.find();
            String tick = regexMatcher.group(1);

            // Step 3: Load planet listing into table
            AllianceDump.loadFileinDB(LOCAL_ALLIANCE_LISTING, conn);

            // Step 4: Populate size rank
            AllianceDump.calculateSizeRank(conn);

            // Step 5: Poluate history tables
            AllianceDump.populateHistory(conn, tick);

            // Step 6: Populate alliance tables from temp tables
            AllianceDump.populateCurrentTables(conn);
            time = System.currentTimeMillis() - time;
            System.out.println("Tick " + tick + ": Alliance Listing Successfully dumped in " + time / 1000 + " seconds");

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }

    }

    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        runTickDump(ALLIANCE_LISTING_URL);
    }
}
